package cn.jji8.mysqlUse.mySqlSimpleUse;

import cn.jji8.mysqlUse.mySqlSimpleUse.column.Conlumn;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.*;
import java.util.Set;

/**
 * 一个与sql建立连接的类
 * */
public class SqlConnect implements AutoCloseable{
    static {
        System.out.println("欢迎使用MysqlSimpleUse1.18，开源地址：https://gitee.com/jji8/MysqlSimpleUse");
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException s) {
            try {
                Path cache = Paths.get("mysql数据库驱动");
                File file = new File(cache.toFile(),"mysql-connector-java-8.0.16.jar");
                if(!file.exists()){
                    System.out.println("释放mysql数据库驱动程序到:"+file.getAbsoluteFile());
                    URL url = SqlConnect.class.getClassLoader().getResource("mysql-connector-java-8.0.16.jar");
                    assert url != null;
                    InputStream inputStream = url.openStream();
                    //noinspection ResultOfMethodCallIgnored
                    cache.toFile().mkdirs();
                    //noinspection ResultOfMethodCallIgnored
                    file.createNewFile();
                    FileOutputStream fileOutputStream = new FileOutputStream(file);
                    while (true){
                        int i = inputStream.read();
                        if(i==-1){
                            break;
                        }
                        fileOutputStream.write(i);
                    }
                    inputStream.close();
                    fileOutputStream.close();
                }
                DependencyLoader.addToClassPath(file.toPath());
                Class.forName("com.mysql.cj.jdbc.Driver");
            } catch (ReflectiveOperationException | IOException e) {
                e.printStackTrace();
                System.out.println("无法加载数据库驱动程序！");
            }
        }
    }
    //数据库的连接
    Connection connection;
    //地址，用户名，密码
    String url,user,password;
    /**
     * 与sql建立连接。
     * @param url 数据库地址,例如：jdbc:mysql://localhost:3306/mymc?useSSL=false&serverTimezone=UTC
     * @param user 数据库用户名
     * @param password 数据库密码
     * */
    public SqlConnect(String url, String user, String password) throws SQLException {
        connection = DriverManager.getConnection(this.url =url,this.user =user,this.password =password);
    }
    /**
     * 获取数据库中的某表，如果没有则会自动创建一个。
     * @param tableName 表名
     * @param ConlumnSet 表中的列集合
     * */
    public SqlTable getTable(String tableName, Set<Conlumn> ConlumnSet){
        return new SqlTable(this,tableName,ConlumnSet);
    }
    /**
     * 关闭连接，并释放底层资源
     * */
    @Override
    public void close() throws Exception {
        connection.close();
    }
    /**
     * 检查连接是否有效，如果无效，将尝试重新连接
     * */
    public void isValid(){
        boolean effective = false;
        try { effective = connection.isValid(10); } catch (SQLException throwables) { throwables.printStackTrace(); }
        if(effective){
            return;
        }
        reconnecting();
    }
    private synchronized void reconnecting(){
        boolean effective = false;
        try { effective = connection.isValid(10); } catch (SQLException throwables) { throwables.printStackTrace(); }
        if(effective){
            return;
        }
        while (true){
            System.out.println("与数据连接已断开，正在尝试重新连接。");
            //如果和数据库已经断开连接，将尝试重新连接
            try {
                connection = DriverManager.getConnection(url, user, password);
                System.out.println("连接成功！");
                return;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("连接失败！");
            }
        }
    }
    /**
     * 获取一个通信
     * */
    sqlStatement getStatement(){
        while (true){
            try {
                return new sqlStatement(connection.createStatement());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("数据库连接可能出现问题，稍后尝试重新连接。");
                isValid();
            }
        }
    }
    /**
     * 处理通信的类
     * */
    class sqlStatement implements AutoCloseable{
        Statement statement;
        sqlStatement(Statement statement){
            this.statement = statement;
        }
        void reconnecting(){
            System.out.println("与数据库通信时发生错误，正在自动修正。");
            try { Thread.sleep(1000); } catch (InterruptedException interruptedException) { interruptedException.printStackTrace(); }
            isValid();
            statement = getStatement().statement;
        }
        /**
         * 执行一个数据库命令
         * */
        public void execute(String s) throws SQLException {
            for (int i=0;i<10;i++){
                if(i>0) System.out.println("重新向数据库发送命令，第"+i+"次。");
                try {
                    statement.execute(s);
                    return;
                }catch (SQLException e){
                    e.printStackTrace();
                    reconnecting();
                }
            }
            throw new SQLException("命令发生失败超过10次！");
        }
        /**
         * 执行一个数据库查询命令
         * */
        public ResultSet executeQuery(String s) throws SQLException {
            for (int i=0;i<10;i++){
                if(i>0) System.out.println("重新向数据库发送命令，第"+i+"次。");
                try {
                    return statement.executeQuery(s);
                }catch (SQLException e){
                    e.printStackTrace();
                    reconnecting();
                }
            }
            throw new SQLException("命令发生失败超过10次！");
        }
        /**
         * 关闭当前连接
         * */
        @Override
        public void close(){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                System.out.println("关闭连接出错");
            }
        }
    }
}
